﻿/*
#------------------------------------------------------------------------------
#-- View Name:		[dbo].[vwWH_IndexOverview_DeletePriority]
#-- Purpose:		Used to review the status of indexes; higher priority = more
#--					reason to delete (high updates; low use)
#--	Last Update:	09/08/2014
#--					For a complete history - please review comments in Source Control
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwWH_IndexOverview_DeletePriority]
AS
SELECT		(CASE		WHEN ([ius_user_updates] + [ius_user_uses]) = 0 THEN 40
						WHEN ([ius_user_updates] + [ius_user_uses]) < 100 THEN 20
						WHEN [ius_user_uses] = 0 AND [ius_user_updates] > 1000000 THEN 100
						WHEN [ius_user_uses] < 100 AND [ius_user_updates] > 100000 THEN 80
						WHEN [ius_user_uses] < 100 AND [ius_user_updates] > 0 THEN 40
						ELSE 0
						END) [priority],
			*
FROM		(
				SELECT		[ius_server_ssd_id],
							[ius_databaseid],
							[ius_tableid],
							[ius_indexid],
							[ius_type],
							MIN([startdate]) [startdate],
							MAX([enddate]) [enddate],
							SUM([ius_user_updates]) [ius_user_updates],
							SUM([ius_user_uses]) [ius_user_uses],
							SUM([ius_system_updates]) [ius_system_updates],
							SUM([ius_system_uses]) [ius_system_uses]
				FROM		[dbo].[vwWH_IndexOverview_Details]
				GROUP BY	[ius_server_ssd_id],
							[ius_databaseid],
							[ius_tableid],
							[ius_indexid],
							[ius_type]
			) review